{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bb8c864e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c588820",
   "metadata": {},
   "source": [
    "### 1. 批量读取多个Excel表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5088e1d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = pd.read_excel(\"采购表.xlsx\", sheet_name=None, parse_dates=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e9744230",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>采购日期</th>\n",
       "      <th>采购物品</th>\n",
       "      <th>采购数量</th>\n",
       "      <th>采购金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>投影仪</td>\n",
       "      <td>5台</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-10</td>\n",
       "      <td>马克笔</td>\n",
       "      <td>5盒</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-15</td>\n",
       "      <td>打印机</td>\n",
       "      <td>1台</td>\n",
       "      <td>298</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        采购日期 采购物品 采购数量  采购金额\n",
       "0 2018-01-06  投影仪   5台  2000\n",
       "1 2018-01-10  马克笔   5盒   300\n",
       "2 2018-01-15  打印机   1台   298"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_list['1月'].head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a69d448f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>采购日期</th>\n",
       "      <th>采购物品</th>\n",
       "      <th>采购数量</th>\n",
       "      <th>采购金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-02-02</td>\n",
       "      <td>文件柜</td>\n",
       "      <td>3个</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-02-04</td>\n",
       "      <td>超市货架</td>\n",
       "      <td>4个</td>\n",
       "      <td>560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-02-06</td>\n",
       "      <td>胶带</td>\n",
       "      <td>5卷</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        采购日期  采购物品 采购数量  采购金额\n",
       "0 2018-02-02   文件柜   3个   500\n",
       "1 2018-02-04  超市货架   4个   560\n",
       "2 2018-02-06    胶带   5卷    40"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_list['2月'].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3de3efc2",
   "metadata": {},
   "source": [
    "### 2. 合并多个工作表到1个"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "db083739",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = pd.concat(df_list.values())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "90e88a87",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(66, 4)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a620d0a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>采购日期</th>\n",
       "      <th>采购物品</th>\n",
       "      <th>采购数量</th>\n",
       "      <th>采购金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-20</td>\n",
       "      <td>展板</td>\n",
       "      <td>2个</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-03-10</td>\n",
       "      <td>封口机</td>\n",
       "      <td>2个</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>广告牌</td>\n",
       "      <td>4个</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        采购日期 采购物品 采购数量  采购金额\n",
       "5 2018-01-20   展板   2个   150\n",
       "4 2018-03-10  封口机   2个   180\n",
       "5 2018-05-21  广告牌   4个   269"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f33eb27d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "采购日期    datetime64[ns]\n",
       "采购物品            object\n",
       "采购数量            object\n",
       "采购金额             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e537970f",
   "metadata": {},
   "source": [
    "### 3. 按采购物品分组写出到不同的工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5be3fd17",
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_writer = pd.ExcelWriter(\n",
    "    '采购表-按采购物品.xlsx', datetime_format=\"YYYY-MM-DD\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "08a830cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "for product, df in df_all.groupby(\"采购物品\"):\n",
    "    df.to_excel(excel_writer, product, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b1e1ab2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d78b8d93",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
